﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using TrainingWorkshop.Model;
using System.Data;
using System.Data.SqlClient;

namespace TrainingWorkshop.NewDAL
{
    public class DepartmentAndTeacherRepository:IDepartmentAndTeacherRepository
    {
        private SqlConnection sqlconn;

        public Teacher ResultInstance;

        public DepartmentAndTeacherRepository(SqlConnection TConn)
        {
            sqlconn = TConn;
            //ResultInstance.DepartmentName = Result.DepartmentName;
            //ResultInstance.TeacherGender = Result.TeacherGender;
            //ResultInstance.TeacherId = Result.TeacherId;
            //ResultInstance.TeacherName = Result.TeacherName;
 
        }

        public List<Teacher> SelectFromDepartmentAndTeacher(Teacher ResultInstance)
        {
            DataSet ds = new DataSet();
            List<Teacher> Result = new List<Teacher>();

            string strSE = "select TeacherId,TeacherName,TeacherGender,DepartmentName,DepartmentID from Teacher a ,Department b where a.DepartmentId=b.DepartmentId and TeacherId=@TeacherID";
                    

            SqlCommand cmd = new SqlCommand(strSE, sqlconn);
            SqlParameter[] par = new SqlParameter[]
            {
                new SqlParameter("@TeacherID",ResultInstance.TeacherId)
            };
            cmd.Parameters.AddRange(par);

            try
            {
                sqlconn.Open();
                SqlDataAdapter adapter = new SqlDataAdapter(strSE, sqlconn);
                adapter.Fill(ds, "Result");
                
            }

            catch (Exception e)
            {

               
            }
            finally
            {
                sqlconn.Close();
            }

            foreach (DataRow row in ds.Tables[0].Rows)
            {
                Department myDepartment = new Department();
                myDepartment.DepartmentName = row[3].ToString();
                myDepartment.DepartmentId = Convert.ToInt16(row[4]);
                Teacher a = new Teacher(Convert.ToInt32(row[0]), row[1].ToString(), row[2].ToString(), myDepartment);

                Result.Add(a);
            }

            return Result;
        }

        public List<Teacher> SelectAllFromDepartmentAndTeacher()
        {
            DataSet ds = new DataSet();
            List<Teacher> Result = new List<Teacher>();

            string strSE = "select TeacherId,TeacherName,TeacherGender,DepartmentName,a.DepartmentID from Teacher a ,Department b where a.DepartmentId=b.DepartmentId";


            SqlCommand cmd = new SqlCommand(strSE, sqlconn);
            //SqlParameter[] par = new SqlParameter[]
            //{
            //    new SqlParameter("@TeacherID",ResultInstance.TeacherId)
            //};
            //cmd.Parameters.AddRange(par);

            try
            {
                sqlconn.Open();
                SqlDataAdapter adapter = new SqlDataAdapter(strSE, sqlconn);
                adapter.Fill(ds, "Result");

                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    Teacher myTeacher = new Teacher();
                    Department myDepartment = new Department();

                    myDepartment.DepartmentId = Convert.ToInt16(row[4]);
                    myDepartment.DepartmentName = row[3].ToString();

                    myTeacher.DepartMent = myDepartment;
                    myTeacher.TeacherGender = row[2].ToString();
                    myTeacher.TeacherName = row[1].ToString();
                    myTeacher.TeacherId = Convert.ToInt16(row[0]);
                    Result.Add(myTeacher);
                }
            }

            catch (Exception e)
            {


            }
            finally
            {
                sqlconn.Close();
            }

           

            return Result;
        }
    }
}
